# coding:utf-8

import json
import string
import time
from datetime import date, timedelta, datetime

import random
from collections import namedtuple, defaultdict
from sqlalchemy.sql import text, func

from common.bid_notify.message import MessageNotify
from common.constant import (
    MULTI_SMS_QUEUE, MEMBERS_PHONE, VoucherStatus, WPH_MULTI_SMS_QUEUE,
    COMMON_API_QUEUE, VoucherStatType, GiveVoucherReason)
from common.models import model_factory
from common.producer import Producer
from common.schedule.opc_celery import celery_app
from common.utils import DB


def get_timestamp_range(days):
    """
    created by chenhj at 2017/7/31
    sample: days为1表示取昨天0点到24点之间的时间戳范围, 2表示前天0点到24点, 以此类推
    """
    today = date.today()
    start_date = today - timedelta(days=days)
    end_date = start_date + timedelta(days=1)
    return namedtuple('TimeStamp', 'start end')(
        start_date.strftime('%s'), end_date.strftime('%s')
    )


@celery_app.task(name='opc_day_stat')
def opc_day_stat():
    """
    created by chenhj at 2017/9/12
    opc日统计
    """
    today = date.today() - timedelta(days=1)
    print(today.strftime('%F'))
    timestamp_range = get_timestamp_range(1)

    # RO 数
    select_ro_cnt_sql = text("""
        select sum(ro_cnt) as ro_cnt from war_zone_daily_statics
        where deleted_at=0 and date=:date
    """)

    # 发布次数
    select_published_cnt_sql = text("""
        select sum(published_count) as sum_publish_cnt from war_zone_daily_statics
        where deleted_at=0 and date=:date
    """)

    # 抢单人数, 抢单次数
    select_partic_cnt_sql = text("""
        select count(0) as bid_cnt, count(distinct supplier_id) as bid_people_cnt
         from partic join user on user.id = partic.supplier_id
         join demand on demand.id = partic.demand_id
         where partic.status != 0 and user.type = 100 and partic.demand_status not in (7, 9)
          and partic.created_at BETWEEN :partic_start and :partic_end
          and partic.deleted_at = 0 
          and partic.is_robot is FALSE and partic.is_training is FALSE
          and user.is_internal is FALSE and user.mobile not in {mobile_list}
    """.format(mobile_list=MEMBERS_PHONE))
    # 付费人数, 付费单数
    select_pay_cnt_sql = text("""
        select count(0) as pay_cnt, count(distinct uid) as pay_people_cnt,
                sum(amt) as pay_amt 
         from bill join user on user.id = bill.uid
        where bill.type = 1 and bill.sub_type in (2, 3) and bill.status = 200
         and bill.created_at BETWEEN :bill_start and :bill_end
         and bill.deleted_at = 0 and user.mobile not in {mobile_list}
    """.format(mobile_list=MEMBERS_PHONE))
    # 到期用户数(弃用)
    select_expired_people_uid_sql = text("""
        select distinct uid from pack where deleted_at=0 and out_date=:out_date
    """)
    # 到期用户付费数(弃用)
    select_expired_people_pay_cnt_sql = text("""
        select count(distinct uid) as exp_peo_pay_cnt from bill
         where uid in :uid and type = 1 and sub_type in (2, 3) and status = 200
         and created_at BETWEEN :bill_start and :bill_end
         and deleted_at = 0
    """)
    # 日消耗汇币数(抢单)
    select_day_cost_bid_point_sql_1 = text("""
        SELECT sum(bid_point) AS sum_bid_point
        FROM bid_cost_record join user on user.id = bid_cost_record.user_id
        WHERE bid_cost_record.deleted_at = 0 
        AND bid_cost_record.cost_type IN (1, 4)
         AND bid_cost_record.created_at BETWEEN :start AND :end
         AND user.is_internal is FALSE and user.mobile not in {mobile_list}
    """.format(mobile_list=MEMBERS_PHONE))

    # 日消耗汇币数(购买派单点)
    select_day_cost_bid_point_sql_2 = text("""
        SELECT sum(bid_point) AS sum_bid_point
        FROM bid_cost_record join user on user.id = bid_cost_record.user_id
        WHERE bid_cost_record.deleted_at = 0 
        AND bid_cost_record.cost_type = 9
         AND bid_cost_record.created_at BETWEEN :start AND :end
         AND user.is_internal is FALSE and user.mobile not in {mobile_list}
    """.format(mobile_list=MEMBERS_PHONE))

    # 日消耗汇币数(其他)
    select_day_cost_bid_point_sql_3 = text("""
        SELECT sum(bid_point) AS sum_bid_point
        FROM bid_cost_record join user on user.id = bid_cost_record.user_id
        WHERE bid_cost_record.deleted_at = 0 
        AND bid_cost_record.cost_type in (7, 8)
         AND bid_cost_record.created_at BETWEEN :start AND :end
         AND user.is_internal is FALSE and user.mobile not in {mobile_list}
    """.format(mobile_list=MEMBERS_PHONE))

    # 日消耗汇币数(购买铜牌会费)
    select_day_cost_bid_point_sql_4 = text("""
        SELECT sum(bid_point) AS sum_bid_point
        FROM bid_cost_record join user on user.id = bid_cost_record.user_id
        WHERE bid_cost_record.deleted_at = 0 
        AND bid_cost_record.cost_type = 11
         AND bid_cost_record.created_at BETWEEN :start AND :end
         AND user.is_internal is FALSE and user.mobile not in {mobile_list}
    """.format(mobile_list=MEMBERS_PHONE))

    # 日退返汇币
    select_day_return_bid_point_sql = text("""
        SELECT sum(bid_point) AS sum_bid_point
        FROM bid_cost_record join user on user.id = bid_cost_record.user_id
        WHERE bid_cost_record.deleted_at = 0 
        AND bid_cost_record.cost_type IN (3, 6)
         AND bid_cost_record.created_at BETWEEN :start AND :end
         AND user.is_internal is FALSE and user.mobile not in {mobile_list}
    """.format(mobile_list=MEMBERS_PHONE))

    # 代金券
    voucher_stat_sql = text("""
        select sum(bid_point) as sum_point, sum(used_bid_point) as sum_used_point from vouchers
        where status=2 and used_date=:date
    """)

    insert_stat_sql = text("""
        insert into opc_day_stat(
          stat_date, ro_cnt, bid_cnt, bid_people_cnt, expired_people_cnt,
            day_cost_bid_point, publish_count,
           expired_pay_cnt, pay_people_cnt, pay_cnt, pay_amt, created_at,
           day_cost_bid_point_dispatch, day_cost_bid_point_other, day_return_bid_point,
           day_cost_bid_point_bronze, voucher_bid_point, used_voucher_bid_point
        ) values(
          :stat_date, :ro_cnt, :bid_cnt, :bid_people_cnt, :expired_people_cnt,
          :day_cost_bid_point, :publish_count,
           :expired_pay_cnt, :pay_people_cnt, :pay_cnt, :pay_amt, :created_at,
           :day_cost_bid_point_dispatch, :day_cost_bid_point_other, :day_return_bid_point,
           :day_cost_bid_point_bronze, :voucher_bid_point, :used_voucher_bid_point
        )
    """)

    with DB() as session:
        ro_cnt_res = session.execute(
            select_ro_cnt_sql,
            {
                'date': today.strftime('%F')
            }
        ).fetchone()

        published_cnt_res = session.execute(
            select_published_cnt_sql,
            {
                'date': today.strftime('%F')
            }
        ).fetchone()

        day_cost_bid_point1 = session.execute(
            select_day_cost_bid_point_sql_1,
            {
                'start': timestamp_range.start,
                'end': timestamp_range.end
            }
        ).fetchone()

        day_cost_bid_point2 = session.execute(
            select_day_cost_bid_point_sql_2,
            {
                'start': timestamp_range.start,
                'end': timestamp_range.end
            }
        ).fetchone()

        day_cost_bid_point3 = session.execute(
            select_day_cost_bid_point_sql_3,
            {
                'start': timestamp_range.start,
                'end': timestamp_range.end
            }
        ).fetchone()

        day_cost_bid_point4 = session.execute(
            select_day_cost_bid_point_sql_4,
            {
                'start': timestamp_range.start,
                'end': timestamp_range.end
            }
        ).fetchone()

        day_return_bid_point = session.execute(
            select_day_return_bid_point_sql,
            {
                'start': timestamp_range.start,
                'end': timestamp_range.end
            }
        ).fetchone()

        partic_cnt_res = session.execute(
            select_partic_cnt_sql,
            {
                'partic_start': timestamp_range.start,
                'partic_end': timestamp_range.end
            }
        ).fetchone()

        pay_cnt_res = session.execute(
            select_pay_cnt_sql,
            {
                'bill_start': timestamp_range.start,
                'bill_end': timestamp_range.end
            }
        ).fetchone()

        voucher_res = session.execute(
            voucher_stat_sql,
            {
                'date': today.strftime('%F')
            }
        ).fetchone()

        expired_people_res = session.execute(
            select_expired_people_uid_sql,
            {
                'out_date': today.strftime('%F')
            }
        ).fetchall()
        uid_list = [x.uid for x in expired_people_res]

        expired_pay_cnt = 0
        if uid_list:
            expired_people_pay_cnt_res = session.execute(
                select_expired_people_pay_cnt_sql,
                {
                    'bill_start': timestamp_range.start,
                    'bill_end': timestamp_range.end,
                    'uid': tuple(uid_list)
                }
            ).fetchone()
            expired_pay_cnt = expired_people_pay_cnt_res.exp_peo_pay_cnt

        session.execute(
            insert_stat_sql,
            {
                'stat_date': today.strftime('%F'),
                'ro_cnt': ro_cnt_res.ro_cnt or 0,
                'publish_count': published_cnt_res.sum_publish_cnt or 0,
                'bid_cnt': partic_cnt_res.bid_cnt,
                'bid_people_cnt': partic_cnt_res.bid_people_cnt,
                'expired_people_cnt': len(uid_list),
                'expired_pay_cnt': expired_pay_cnt,
                'pay_people_cnt': pay_cnt_res.pay_people_cnt,
                'pay_cnt': pay_cnt_res.pay_cnt,
                'pay_amt': pay_cnt_res.pay_amt or 0,
                'created_at': time.time(),
                'day_cost_bid_point': day_cost_bid_point1.sum_bid_point or 0,
                'day_cost_bid_point_dispatch': day_cost_bid_point2.sum_bid_point or 0,
                'day_cost_bid_point_other': day_cost_bid_point3.sum_bid_point or 0,
                'day_return_bid_point': day_return_bid_point.sum_bid_point or 0,
                'day_cost_bid_point_bronze': day_cost_bid_point4.sum_bid_point or 0,
                'voucher_bid_point': voucher_res.sum_point or 0,
                'used_voucher_bid_point': voucher_res.sum_used_point or 0,
            }
        )


@celery_app.task(name='invite_msg_task')
def invite_msg_task(msg_type=0, leave=1000):
    """
    create by ld_wu 2017-10-24
    营销用户短信
    1、解绑虚拟号
    2、根据当前要发送的类型取出当前需要发短信的客户
    3、绑定虚拟号
    4、生产者写入短信队列
    5、启动下次发送任务
    :return:
    """
    sql = MsgMap.get_sql(msg_type)
    print(sql)

    update_first_date_sql = """
        update user set 
        sms_marketing_first_date = '{today}', 
        sms_current_send_date = '{today}'
        where mobile in {mobiles}
    """

    update_current_date_sql = """
        update user set 
        sms_current_send_date = '{today}'
        where mobile in {mobiles}
    """

    def unbind_number():
        """
        解绑短信虚拟号
        :return:
        """
        # unbind_sql = """
        #     update virtual_numbers
        #     set valid = 0,
        #     updated_at = {}
        #     WHERE
        #     for_sms = true and valid = 1 and tel_a not in ('13424710263','15889797200')
        # """
        unbind_sql = """
            update virtual_numbers
            set valid = 0,
            updated_at = {}
            WHERE
            for_sms = true and valid = 1 and reserved = false
        """
        session.execute(unbind_sql.format(int(time.time())))

    def bind_number():
        """
        create by ld_wu 2017-10-25
        绑定短信虚拟号
        :return:
        """
        bind_sql = """
        insert into virtual_numbers 
        (tel_a, tel_x, for_sms, created_at, updated_at) 
        values {inset_values}
        """
        get_numbers_sql = """
        select tel from number_pool_for_lt where reserved=false
        order by updated_at asc
        limit 100;
        """
        fresh_numbers_updated_at_sql = """
        update number_pool_for_lt
        set updated_at = UNIX_TIMESTAMP()
        where tel in {tels};
        """
        unbind_number()
        virtual_mobiles = list()
        if mobile_list:
            mobile_length = len(mobile_list)
            virtual_mobiles = session.execute(
                get_numbers_sql
            ).fetchall()
            virtual_mobiles = [i.tel for i in virtual_mobiles]

            # 更新 number_pool_for_lt 的 updated_at
            tmp_text = list(
                map(lambda x: '\'{}\''.format(x), virtual_mobiles)
            )
            virtual_mobiles_text = '({})'.format(', '.join(tmp_text))
            session.execute(
                fresh_numbers_updated_at_sql.format(
                    tels=virtual_mobiles_text,
                )
            )

            # 洗牌，打乱虚拟号顺序
            random.shuffle(virtual_mobiles)
            virtual_mobiles = virtual_mobiles[:mobile_length]
            timestamp_list = [int(time.time())] * mobile_length
            values = list(zip(
                mobile_list,
                virtual_mobiles,
                [1] * mobile_length,
                timestamp_list,
                timestamp_list,
            ))
            inset_values = map(
                lambda x: '(\'{}\', \'{}\', {}, {}, {})'.format(*x), values
            )
            if values:
                value_text = ', '.join(inset_values)
                session.execute(
                    bind_sql.format(inset_values=value_text)
                )
        return virtual_mobiles

    def gen_content():
        """
        create by ld_wu 2017-10-25
        生成群发短信内容
        :return:
        """
        number_to_mobile = dict(zip(numbers, mobile_list))
        invalid_letters = (
            string.ascii_letters + string.digits + string.punctuation
        )
        for number in numbers:
            name = mobile_dict.get(number_to_mobile.get(number)) or ''
            if name and name != '工程商':
                name = name[0]
                if name in invalid_letters:
                    name = ''
                else:
                    name = '{}总'.format(name)
            elif name and name[:2] == '店主':
                name = ''
            else:
                name = ''

            tmp_element = {
                'mobile': number,
                'content': MsgMap.msg_mapping.get(msg_type).format(name)
            }
            to_send_list.append(tmp_element)

            # 首次发送的追加一条短信
            if msg_type == MsgMap.NEW:
                external_content = {
                    'mobile': number,
                    'content': 'P6的屏幕，挂在5楼商场外墙，播放视频用。朋友推荐说你家可以做'
                }
                external_content_list.append(external_content)

    with DB() as session:
        try:
            today = date.today()
            mobile_list = session.execute(sql).fetchall()
            print(mobile_list)
            print(msg_type, leave)
            mobile_dict = dict(mobile_list)
            mobile_list = mobile_dict.keys()
            mobile_list_text = list(
                map(lambda x: '\'{}\''.format(x), mobile_list)
            )
            mobile_text = '({})'.format(', '.join(mobile_list_text))
            new_msg_type = msg_type
            if msg_type == MsgMap.NEW:
                if leave <= 0 or not mobile_list:
                    # 今日新发完成后进入按天发送
                    new_msg_type = MsgMap.get_next_type(msg_type)
                if mobile_list:
                    leave -= 100
                    # 首次发送更新首次时间跟最近发送时间
                    session.execute(update_first_date_sql.format(
                        today=today,
                        mobiles=mobile_text
                    ))
            else:
                if not mobile_list:
                    # 某种类型发送完以后需要进行下一个类型的发送
                    new_msg_type = MsgMap.get_next_type(msg_type)
                else:
                    # 再次更新最近发送时间
                    session.execute(update_current_date_sql.format(
                        today=today,
                        mobiles=mobile_text
                    ))
            if msg_type == MsgMap.TWELVE_DAY and not mobile_list:
                # 所有发送完成以后直接返回
                return
            numbers = bind_number()
            to_send_list = list()
            external_content_list = list()
            gen_content()
            print(to_send_list)
            now = datetime.utcnow()
            multi_sms_producer = Producer(MULTI_SMS_QUEUE)
            multi_sms_producer.send(json.dumps(to_send_list))

            if external_content_list:
                external_msg_task.apply_async(
                    (external_content_list, new_msg_type, leave),
                    eta=now + timedelta(seconds=60 * 2),
                    expires=now + timedelta(seconds=60 * 5)
                )
            else:
                invite_msg_task.apply_async(
                    (new_msg_type, leave),
                    eta=now + timedelta(seconds=60 * 5),
                    expires=now + timedelta(seconds=60 * 8)
                )
        except Exception as e:
            print(e)
            session.rollback()


class MsgMap(object):

    NEW = 0
    TWO_DAY = 1
    FOUR_DAY = 3
    SEVEN_DAY = 6
    TWELVE_DAY = 11

    msg_type_mapping = {
        NEW: TWO_DAY,
        TWO_DAY: FOUR_DAY,
        FOUR_DAY: SEVEN_DAY,
        SEVEN_DAY: TWELVE_DAY
    }

    msg_mapping = {
        NEW: '{}，您这边还在做LED显示屏吗？我这边要装一块大概80平的全彩屏，30万能做下来吗？',
        TWO_DAY: '{}你好，你这能修户外大屏幕吗？我们商场外墙挂的LED大屏幕有差不多一半不亮，四楼高需要下绳，能修尽快回复我。',
        FOUR_DAY: '你好，你这边门头上走字的屏幕多少钱？门头宽6米多，3000块能做吗？',
        SEVEN_DAY: '{}您那还有租赁大屏幕吗，p4.71户外的，公司做活动用，20平左右，能做的话给个报价',
        TWELVE_DAY: '你好，P8全彩，100多方的户外屏，你这边可以做吗？',
    }

    date_sql = """
    select mobile, nickname from user where 
    sms_marketing_obj=TRUE 
    and sms_marketing_level = 0
    and sms_marketing_first_date = '{sms_marketing_first_date}'
    and sms_marketing_respond = FALSE
    and sms_current_send_date != '{today}'
    limit 100
    """

    mapping = {
        NEW: """
        select mobile, nickname from user where 
        sms_marketing_obj=TRUE 
        and sms_marketing_level = 0
        and sms_marketing_first_date is null
        and sms_marketing_respond = FALSE
        and sms_current_send_date is null
        limit 100
        """
    }

    @classmethod
    def get_sql(cls, msg_type):
        today = date.today()
        sms_date = today - timedelta(days=msg_type)
        return cls.mapping.get(msg_type, cls.date_sql).format(
            sms_marketing_first_date=sms_date,
            today=today
        )

    @classmethod
    def get_next_type(cls, msg_type):
        return cls.msg_type_mapping.get(msg_type, msg_type)


@celery_app.task(name='unbind_expired_virtual_number')
def unbind_expired_virtual_number():
    """
    add by zhouhl 2017-12-25
    定时解绑7天未使用的虚拟号关系
    """
    timestamp_range = get_timestamp_range(7)

    find_expired_virtual_number_sql = text("""
        select
            tel_x
        from
            virtual_numbers
        where
            valid=1
            and reserved=false
            and updated_at<:updated_at;
    """)

    sub_virtual_number_use_count_sql = text("""
        update
            number_pool_for_lt
        set
            use_count=use_count-1,
            updated_at=UNIX_TIMESTAMP()
        where
            tel=:tel;
    """)

    unbind_expired_virtual_number_sql = text("""
        update
            virtual_numbers
        set
            valid=0,
            updated_at=UNIX_TIMESTAMP()
        where
            valid=1
            and reserved=false
            and updated_at<:updated_at;
    """)
    with DB() as session:
        tmp_result = session.execute(
            find_expired_virtual_number_sql,
            {
                'updated_at': timestamp_range.start,
            }
        ).fetchall()
        for i in tmp_result:
            session.execute(
                sub_virtual_number_use_count_sql,
                {
                    'tel': i.tel_x
                }
            )

        session.execute(
            unbind_expired_virtual_number_sql,
            {
                'updated_at': timestamp_range.start,
            }
        )


@celery_app.task(name='set_demand_done')
def set_demand_done():
    """
    add by zhouhl 2017-12-25
    定时将10天前发布的项目状态调整为“圆满完成”
    只修改 '进行中'，'竞价结束'，'二次竞价中'，'免费竞价中'，'抢单结束'这几个状态的订单
    """
    timestamp_range = get_timestamp_range(10)
    set_demand_done_sql = text("""
        update
            demand
        set
            status=200
        where
            status in (10,15,20,30,100)
            and published_at<:published_at;
    """)
    with DB() as session:
        session.execute(
            set_demand_done_sql,
            {
                'published_at': timestamp_range.start,
            }
        )


@celery_app.task(name='external_msg_task')
def external_msg_task(external_content_list, *args):
    """
    create by ld_wu 2018-05-30
    首次发送拉新后追加发送额外短信
    """
    multi_sms_producer = Producer(MULTI_SMS_QUEUE)
    multi_sms_producer.send(json.dumps(external_content_list))
    now = datetime.utcnow()
    invite_msg_task.apply_async(
        args,
        eta=now + timedelta(seconds=60 * 5),
        expires=now + timedelta(seconds=60 * 8)
    )


@celery_app.task(name='set_seven_days_marketing_sms_done')
def set_seven_days_marketing_sms_done():
    """
    add by zhouhl 2018-06-28
    将已经发送了7天营销短信，仍未发满4条的营销用户设置为不发送短信
    status=20时停止营销
    """
    seven_days_ago = date.today() - timedelta(days=7)
    update_user_marketing_info_july_sql = text("""
        update
            user_marketing_info_july
        set
            status=20
        where
            status=10
            and first_send_date<:seven_days_ago;
    """)
    with DB() as session:
        session.execute(
            update_user_marketing_info_july_sql,
            {
                'seven_days_ago': seven_days_ago
            }
        )


@celery_app.task(name='voucher_expired_task')
def voucher_expired_task():
    """
    create_by ld_wu 2018-07-05
    代金券过期定时任务, 同时计算当前存量汇币到昨天的存量统计中
    """
    sql = """
        update 
            vouchers
        set 
            status = :expired_status
        where
            status = :available_status and
            expired_date < :today and
            reason = :reason
    """

    stock_sql = """
        update
            stock_day_stat
        set 
            stock_bid_point = :stock_bid_point
        where
            stat_date = :stat_date
    """

    with DB() as session:
        today = date.today()
        for reason in GiveVoucherReason:
            res = session.execute(
                sql,
                dict(
                    expired_status=VoucherStatus.EXPIRED.value,
                    available_status=VoucherStatus.AVAILABLE.value,
                    today=today.strftime('%F'),
                    reason=reason.value
                )
            )
            count = res.rowcount
            if count:
                common_producer = Producer(COMMON_API_QUEUE)
                common_producer.send(
                    json.dumps(
                        dict(
                            backend='voucher_stat',
                            func='day_stat',
                            kwargs=dict(
                                voucher_stat_type=VoucherStatType.EXPIRED.value,
                                reason=reason.value,
                                stat_cnt=count
                            )
                        )
                    )
                )

        # 计算存量
        stock_bid_point = int(session.query(
            model_factory.pack
        ).with_entities(
            func.sum(model_factory.pack.bid_point)
        ).first()[0] or 0)

        yesterday = today - timedelta(days=1)
        session.execute(
            stock_sql,
            dict(
                stock_bid_point=stock_bid_point,
                stat_date=yesterday.strftime('%F'),
            )
        )

        # 可用代金券
        vouchers = session.query(
            model_factory.vouchers
        ).filter(
            model_factory.vouchers.expired_date >= today,
            model_factory.vouchers.status == VoucherStatus.AVAILABLE.value
        ).all()

        inventory_dict = cal_voucher_inventory(vouchers)
        # 写入库存
        insert_voucher_inventory(session, inventory_dict)


def cal_voucher_inventory(vouchers):
    """
    create by ld_wu 2018-07-19
    计算代金券库存
    :param vouchers:
    :return:
    """
    user_id_dict = defaultdict(set)
    inventory_dict = defaultdict(int)
    user_ids = set()
    res_dict = defaultdict(int)
    voucher_total = len(vouchers)

    for voucher in vouchers:
        user_ids.add(voucher.user_id)
        user_id_dict[voucher.reason].add(voucher.user_id)
        inventory_dict[voucher.reason] += 1

    inventory = VoucherStatType.INVENTORY

    # 总计库存用户数
    res_dict[inventory.stat_user_key] = len(user_ids)
    # 总计代金券张数
    res_dict[inventory.stat_key] = voucher_total

    # 各种理由的用户数
    for reason, value in user_id_dict.items():
        key = GiveVoucherReason(reason).stat_user_key(inventory)
        res_dict[key] = len(value)

    # 各种理由代金券数量
    for reason, value in inventory_dict.items():
        key = GiveVoucherReason(reason).stat_key(inventory)
        res_dict[key] = value

    return res_dict


def insert_voucher_inventory(session, inventory_dict):
    """
    create by ld_wu 2018-07-19
    更新代金券统计库存数量
    :return:
    """
    today = date.today()
    for stat_type, stat_cnt in inventory_dict.items():
        stat = session.query(
            model_factory.voucher_daily_stat
        ).filter(
            model_factory.voucher_daily_stat.stat_date == today,
            model_factory.voucher_daily_stat.stat_type == stat_type
        ).first()

        if not stat:
            stat = model_factory.voucher_daily_stat.create(
                session,
                stat_date=today,
                stat_type=stat_type
            )
        stat.stat_cnt = stat_cnt


@celery_app.task(name='voucher_expired_notify')
def voucher_expired_notify():
    """
    created by chenhj at 2018/7/5
    代金券到期当天提醒客户
    """
    expired_date = date.today()

    sql = """
        select user.id, user.mobile, sum(vouchers.bid_point) as bid_point from
        user join vouchers on user.id = vouchers.user_id
        where vouchers.expired_date='{}' and vouchers.status={} group by user.id
    """.format(expired_date.strftime('%F'), VoucherStatus.AVAILABLE.value)

    with DB() as session:
        content = (
            '您有价值{}元代金券今日到期，立即打开“万屏汇”微信公众号免费抢单吧。回T退订'
        )
        res = session.execute(sql).fetchall()
        to_send_list = [dict(
            mobile=each.mobile,
            content=content.format(each.bid_point)
        ) for each in res]
        if to_send_list:
            multi_sms_producer = Producer(WPH_MULTI_SMS_QUEUE)
            multi_sms_producer.send(json.dumps(to_send_list))

            MessageNotify.app_voucher_ending_notify(session, res)


if __name__ == '__main__':
    # opc_day_stat()
    # set_demand_done()
    # invite_msg_task()
    pass
